const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
  host: '43.143.253.188',
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4',
  port: 3306,
  connectTimeout: 60000,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true,
  ssl: false
};

// 需要迁移的表和字段配置
const tablesToMigrate = [
  // 核心表
  { table: 'users', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'characters', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_locations', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'chat_messages', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_inventories', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'private_messages', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'system_messages', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_resources', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_gem_inventories', hasCreatedAt: true, hasUpdatedAt: true },

  // 其他表
  { table: 'social_relations', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'shop_goods', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'player_online_times', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'mount_trainings', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'mount_bases', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'monthly_cards', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'map_areas', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'items', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'gem_bases', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'gem_attributes', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'factions', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'faction_members', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'equipment_bases', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_stones', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_skills', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_residences', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_mounts', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_medicines', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_equipment_inventories', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_detailed_attributes', hasCreatedAt: false, hasUpdatedAt: true },
  { table: 'character_attributes', hasCreatedAt: false, hasUpdatedAt: true },
  { table: 'character_attribute_points', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'announcements', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'gem_minings', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'equipment_gem_sockets', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'character_religions', hasCreatedAt: false, hasUpdatedAt: true },
  { table: 'character_equipment', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'npc_clusters', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'npc_details', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'basic_items', hasCreatedAt: true, hasUpdatedAt: true },

  // Casino相关表
  { table: 'casino_dice_rounds', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_chibi_rounds', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_dice_bets', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_chibi_bets', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_rock_paper_scissors', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_rankings', hasCreatedAt: false, hasUpdatedAt: true },
  { table: 'casino_configs', hasCreatedAt: true, hasUpdatedAt: true },
  { table: 'casino_operation_logs', hasCreatedAt: true, hasUpdatedAt: true }
];

async function checkTableExists (connection, tableName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.tables 
       WHERE table_schema = ? AND table_name = ?`,
      [dbConfig.database, tableName]
    );
    return rows[0].count > 0;
  } catch (error) {
    console.error(`检查表 ${tableName} 是否存在时出错:`, error.message);
    return false;
  }
}

async function checkColumnExists (connection, tableName, columnName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = ? AND column_name = ?`,
      [dbConfig.database, tableName, columnName]
    );
    return rows[0].count > 0;
  } catch (error) {
    console.error(`检查列 ${tableName}.${columnName} 是否存在时出错:`, error.message);
    return false;
  }
}

async function getColumnDefinition (connection, tableName, columnName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
       FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = ? AND column_name = ?`,
      [dbConfig.database, tableName, columnName]
    );
    return rows[0];
  } catch (error) {
    console.error(`获取列定义 ${tableName}.${columnName} 时出错:`, error.message);
    return null;
  }
}

async function migrateTable (connection, tableConfig) {
  const { table, hasCreatedAt, hasUpdatedAt } = tableConfig;

  console.log(`\n🔄 处理表: ${table}`);

  // 检查表是否存在
  const tableExists = await checkTableExists(connection, table);
  if (!tableExists) {
    console.log(`⚠️  表 ${table} 不存在，跳过`);
    return { success: true, skipped: true };
  }

  const migrations = [];

  // 处理 createdAt 字段
  if (hasCreatedAt) {
    const createdAtExists = await checkColumnExists(connection, table, 'createdAt');
    const created_atExists = await checkColumnExists(connection, table, 'created_at');

    if (createdAtExists && !created_atExists) {
      const columnDef = await getColumnDefinition(connection, table, 'createdAt');
      if (columnDef) {
        const newDefinition = `\`created_at\` ${columnDef.COLUMN_TYPE} ${columnDef.IS_NULLABLE === 'NO' ? 'NOT NULL' : ''} ${columnDef.COLUMN_DEFAULT ? `DEFAULT ${columnDef.COLUMN_DEFAULT}` : ''} ${columnDef.EXTRA} ${columnDef.COLUMN_COMMENT ? `COMMENT '${columnDef.COLUMN_COMMENT}'` : ''}`;
        migrations.push({
          sql: `ALTER TABLE \`${table}\` CHANGE COLUMN \`createdAt\` ${newDefinition}`,
          description: `重命名 createdAt -> created_at`
        });
      }
    } else if (created_atExists) {
      console.log(`✅ ${table}.created_at 已存在`);
    } else {
      console.log(`⚠️  ${table} 没有 createdAt 字段`);
    }
  }

  // 处理 updatedAt 字段
  if (hasUpdatedAt) {
    const updatedAtExists = await checkColumnExists(connection, table, 'updatedAt');
    const updated_atExists = await checkColumnExists(connection, table, 'updated_at');

    if (updatedAtExists && !updated_atExists) {
      const columnDef = await getColumnDefinition(connection, table, 'updatedAt');
      if (columnDef) {
        const newDefinition = `\`updated_at\` ${columnDef.COLUMN_TYPE} ${columnDef.IS_NULLABLE === 'NO' ? 'NOT NULL' : ''} ${columnDef.COLUMN_DEFAULT ? `DEFAULT ${columnDef.COLUMN_DEFAULT}` : ''} ${columnDef.EXTRA} ${columnDef.COLUMN_COMMENT ? `COMMENT '${columnDef.COLUMN_COMMENT}'` : ''}`;
        migrations.push({
          sql: `ALTER TABLE \`${table}\` CHANGE COLUMN \`updatedAt\` ${newDefinition}`,
          description: `重命名 updatedAt -> updated_at`
        });
      }
    } else if (updated_atExists) {
      console.log(`✅ ${table}.updated_at 已存在`);
    } else {
      console.log(`⚠️  ${table} 没有 updatedAt 字段`);
    }
  }

  // 执行迁移
  let successCount = 0;
  for (const migration of migrations) {
    try {
      console.log(`   🔄 ${migration.description}`);
      await connection.execute(migration.sql);
      console.log(`   ✅ ${migration.description} 成功`);
      successCount++;
    } catch (error) {
      console.error(`   ❌ ${migration.description} 失败:`, error.message);
    }
  }

  return {
    success: successCount === migrations.length,
    total: migrations.length,
    successCount
  };
}

async function main () {
  console.log('🚀 开始数据库字段迁移...');
  console.log(`📋 目标数据库: ${dbConfig.host}/${dbConfig.database}`);
  console.log(`📊 需要处理的表数量: ${tablesToMigrate.length}`);

  let connection;

  try {
    // 连接数据库
    console.log('\n🔌 连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');

    // 执行迁移
    let totalSuccess = 0;
    let totalTables = 0;
    let skippedTables = 0;

    for (const tableConfig of tablesToMigrate) {
      const result = await migrateTable(connection, tableConfig);
      totalTables++;

      if (result.skipped) {
        skippedTables++;
      } else if (result.success) {
        totalSuccess++;
      }
    }

    // 输出统计结果
    console.log('\n📊 迁移完成统计:');
    console.log(`   总表数: ${totalTables}`);
    console.log(`   成功: ${totalSuccess}`);
    console.log(`   跳过: ${skippedTables}`);
    console.log(`   失败: ${totalTables - totalSuccess - skippedTables}`);

    if (totalSuccess > 0) {
      console.log('\n✅ 数据库字段迁移完成！');
      console.log('\n📝 接下来需要执行以下步骤：');
      console.log('1. 重启应用程序');
      console.log('2. 测试功能是否正常');
    } else {
      console.log('\n⚠️  没有执行任何迁移操作');
    }

  } catch (error) {
    console.error('\n❌ 迁移过程中发生错误:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 执行主函数
main().catch(console.error);
